﻿using AdoNetCore.AseClient;
using System;
using System.Data;
using System.Text;


namespace DBEntitie
{
  /// <summary>
  /// sybase ase 数据库连接
  /// </summary>
  public class SyBaseEntities : IDisposable
  {
    private readonly string _connectionString = ConfigurationManager.ConnectionStrings("Entities");
    public readonly AseConnection Client;

    /// <summary>
    /// 如果不使用 appsettings.json 的 Entities 配置连接数据库
    /// 可用传入 connectString 例：
    /// "Data Source=192.168.0.205;Port=5000;Database=LH0001;UID=sa;PWD=sqlsql;charset=cp850"
    /// <param name="connectString"></param>
    public SyBaseEntities(string connectString = null)
    {
      Client = string.IsNullOrWhiteSpace(connectString) ? new AseConnection(_connectionString) : new AseConnection(connectString);
      Client.Open();
    }

    /// <summary>
    /// 执行无结果集SQL语句
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public int Execute(string sql)
    {
      sql = Encoding.GetEncoding("ibm850").GetString(Encoding.GetEncoding("gbk").GetBytes(sql));
      var command = new AseCommand(sql, Client);
      try
      {
        return command.ExecuteNonQuery();
      }
      catch (Exception ex)
      {
        throw new Exception($"SQL:{sql}", ex);
      }
    }       

    /// <summary>
    /// 表格结果集 sql语句 查询
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public DataTable QueryTable(string sql)
    {
      var ds = new DataSet();
      sql = Encoding.GetEncoding("ibm850").GetString(Encoding.GetEncoding("gbk").GetBytes(sql));
      try
      {
        var dataAdapter = new AseDataAdapter(sql, Client);        
        dataAdapter.Fill(ds);
      }
      catch (AseException)
      {
        return null;
      }
      var sourcetable = ds.Tables.Count <= 0 || ds.Tables[0].Rows.Count <= 0 ? null : ds.Tables[0];
      if (sourcetable == null) return sourcetable;
      var table = sourcetable.Clone();

      foreach (DataRow rows in sourcetable.Rows)
      {
        var row = table.NewRow();
        for (var i = 0; i < rows.ItemArray.Length; i++)
        {
          try
          {
            var item = rows.ItemArray[i];
            var t = Encoding.GetEncoding("gbk").GetString(Encoding.GetEncoding("ibm850").GetBytes(item.ToString() ?? ""));
            row[i] = Encoding.GetEncoding("gbk").GetString(Encoding.GetEncoding("ibm850").GetBytes(item.ToString() ?? ""));
          }
          catch (Exception)
          {
            // ignored
          }
        }
        table.Rows.Add(row);
      }
      return table;
    }   
   
    /// <summary>
    /// 返回结果集 第一条记录
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public DataRow FirstOrDefault(string sql)
    {
      var table = QueryTable(sql);
      return table?.Rows[0];
    }  

    public void Dispose()
    {
      Client.Close();
      Client.Dispose();
    }
  }
}
